<%@ page contentType="text/html;charset=UTF-8"%>
<%@ include file="/common/taglibs.jsp"%>
<%@ include file="../common.jsp"%>
<%@ page import="cn.firstsoft.firstframe.admin.*,java.util.*,java.sql.*,javax.sql.*,javax.naming.*" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
<head>
<title>管理控制台 - 数据中心</title>
<%@ include file="/common/meta2.jsp"%>
<link href="../style.css" type="text/css" rel=stylesheet>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
</head>
<body leftmargin="0" topmargin="0" marginwidth="0" marginheight="0">
请选择执行SQL语句的类型，然后在文本框中输入SQL语句，然后点执行按钮！<a href="index.jsp">点这里返回数据中心列表</a><br>
执行大数据量sql会执行很长时间，在进行查询语句时尽量加上条件过滤，查询数据不要超过100行，否则会造成浏览器无响应<br>
<%
	if ("true".equals(request.getParameter("isfirst"))) {
		session.removeAttribute("flag");
	}
	String state="";
	
	String dmlddl=request.getParameter("dmlddl")==null?"1":request.getParameter("dmlddl");
	String sql=request.getParameter("sql")==null?"":request.getParameter("sql");
	sql = new String(sql.getBytes("ISO8859-1"),"UTF-8");
	String returnsql=sql;
	String s_m=request.getParameter("s_m")==null?"1":request.getParameter("s_m");
	String datasource=request.getParameter("datasource")==null?"DefaultDS":request.getParameter("datasource");
	String flag=""+Math.random();
	String sflag=request.getParameter("flag")==null?"":request.getParameter("flag");
	String static_replace_str="@@@@@z@@@@@@@x@@@@@@@@q@@@";
	String static_t_replace_str="######z########x###########q#####";
	if(session.getAttribute("flag")==null)session.setAttribute("flag","");
	if(!sql.equals("") && session.getAttribute("flag")!=null &&  session.getAttribute("flag").equals(sflag)){
		sql=sql.replaceAll("\r"," ");
		sql=sql.replaceAll("\n"," ");
		String cursql="";
		Context init=null;
		javax.sql.DataSource ds=null;
		Connection conn =null;
		PreparedStatement ps =null;
		
		if(dmlddl.equals("1")){
			try {
					init=new InitialContext();
					String jndi_name = "jdbc/" + datasource;
					if ("TOMCAT".equals(Environment.getWebServerName()))
						jndi_name = "java:comp/env/" + jndi_name;
						
					ds=(javax.sql.DataSource)init.lookup(jndi_name);
					
					conn = ds.getConnection();
					ps = conn.prepareStatement(sql);
					ResultSet rs=ps.executeQuery();
					ResultSetMetaData rsm=rs.getMetaData();
					int colCount = rsm.getColumnCount();
					//String colName = rsm.getColumnName(i);
					
					out.println("<script language=\"javascript\">var obj=window.open(\"about:blank\",\"查询结果\",'height=480, width=600, top=0, left=0, toolbar=no, menubar=no, scrollbars=yes,resizable=yes,location=no, status=no');");
					out.println("var doc=obj.document;");
					out.println("doc.write(\"<html><head><META HTTP-EQUIV='Content-Type' CONTENT='text/html; charset=UTF-8'>\");");
					out.println("doc.write(\"<link rel='STYLESHEET' type='text/css' href='../../ui/kingdee/style.css'>\");");
					out.println("doc.write(\"<title>查询结果:"+sql+"</title>\");");
					out.println("doc.write(\"</head><body>\");");
					
					out.println("doc.write(\"<table width='100%' border='0' cellspacing=0 cellpadding='0' height='21'>\");");
					out.println("doc.write(\"<tr><td>&nbsp;</td>\");");
					out.println("doc.write(\"<td width='100%' align='left' valign='top'>\");");
					out.println("doc.write(\"<table width='100%'  border='0' cellspacing='0' cellpadding='0'>\");");
					out.println("doc.write(\"<tr><td height='4'></td></tr>\");");
					out.println("doc.write(\"<tr><td>&nbsp;<font color='black'><b>>>&nbsp;查询结果......</b></font></td></tr></table>\");");
					out.println("doc.write(\"</td><td valign='top'>\");");
					out.println("doc.write(\"</td><td>&nbsp;</td></tr></table>\");");
					
					out.println("doc.write(\"<table width='100%' border='0' align='center' cellpadding='0' cellspacing='1' bgcolor='#006699'><tr bgcolor='#669999'>\");");

					for(int i=1;i<=colCount;i++){
						String colName = rsm.getColumnName(i);
						int colTypei = rsm.getColumnType(i);
						String colType="";
						switch (colTypei){
							case 2:
								colType="数字型";
								break;
							case 12:
								colType="字符串";
								break;
							case 93:
								colType="时间戳";
								break;
							case 91:
								colType="日期型";
								break;
							default:
								colType+=colTypei;
								break;
						}
						out.println("doc.write(\"<td align='center'>\");");
						out.println("doc.write(\""+colName+"("+colType+")\");");
						out.println("doc.write(\"</td>\");");
					}
					out.println("doc.write(\"</tr>\");");
					boolean b=true;
					while(rs.next()){
						b=!b;
						String color="#FFFFFF";
						if(b)color="#99CCCC";
						out.println("doc.write(\"<tr bgcolor='"+color+"'>\");");
						for(int i=1;i<=colCount;i++){
						  Object obj=null;
							int colTypei = rsm.getColumnType(i);
							switch (colTypei){
								case 93:
									obj=rs.getTimestamp(i);
									break;
								case 91:
									obj=rs.getTimestamp(i);
									break;
								default:
									obj=rs.getObject(i);
									break;
							}
							
							String temp="";
							if(obj!=null){
								temp=obj.toString();
								temp=temp.replaceAll("\"","&quot;");
								temp=temp.replaceAll("<","&lt;");
								temp=temp.replaceAll(">","&gt;");
								temp=temp.replaceAll("'","&acute;");
								temp=temp.replaceAll(" ","&nbsp;");
								//temp=temp.replaceAll("&","&amp;");
								temp=temp.replaceAll("\n","<BR>");
								temp=temp.replaceAll("\r","<BR>");
							}
							out.println("doc.write(\"<td align='center'>\");");
							out.println("doc.write(\""+temp+"\");");
							out.println("doc.write(\"</td>\");");
						}
						out.println("doc.write(\"</tr>\");");
					}
					out.println("doc.write(\"</table>\");");
					out.println("doc.write(\"</body></html>\");");
					
					out.println("</script>");
					state="执行成功.....";
			} catch (Exception e) {
					state="执行查询时异常,可能的原因是："+e.toString();
			} finally {
					try {
						if(ps!=null)ps.close();
							if (conn != null) conn.close();
					} catch (Exception ex) {
							out.println(ex.getMessage());
					}
			}
		}
		else{
			if(s_m.equals("1")){
				try {
						init=new InitialContext();
						String jndi_name = "jdbc/" + datasource;
						if ("TOMCAT".equals(Environment.getWebServerName()))
						jndi_name = "java:comp/env/" + jndi_name;						
						ds=(javax.sql.DataSource)init.lookup(jndi_name);
						conn = ds.getConnection();
						ps = conn.prepareStatement(sql);
						ps.execute();
						state="执行成功.....";
				} catch (SQLException e) {
						state="执行单条语句时异常,可能的原因是："+e.getMessage();
				} finally {
						try {
							if(ps!=null)ps.close();
								if (conn != null) conn.close();
						} catch (Exception ex) {
								out.println(ex.getMessage());
						}
				}
			}
			else{
					StringBuffer sqlb=new StringBuffer(sql);
					try{
						int curnum=0;
						ArrayList quotes=new ArrayList();
						ArrayList  semicolons=new ArrayList();
						
						while(sql.indexOf("'",curnum)!=-1){
							int[] quote=new int[2];
							quote[0]=sql.indexOf("'",curnum);
							curnum=quote[0]+1;
							if(sql.indexOf("'",curnum)!=-1){
								quote[1]=sql.indexOf("'",curnum);
								curnum=quote[1]+1;
							}
							else{
								cursql=sql.substring(quote[0]);
							}
							quotes.add(quote);
						}
						
						for (int i = 0; i<quotes.size(); i++){
							int[] quote=(int[])quotes.get(i);
							System.out.println ("i    "+i+"    "+quote[0]+"    "+quote[1]);
						}
						
						curnum=0;
			
						while(sql.indexOf(";",curnum)!=-1){
							semicolons.add(new Integer(sql.indexOf(";",curnum)));
							curnum=sql.indexOf(";",curnum)+1;
						}
						
						for (int i = 0; i<semicolons.size(); i++){
							int semicolon=((Integer)semicolons.get(i)).intValue();
			
							for (int j = 0; j<quotes.size(); j++){
								int[] quote=(int[])quotes.get(j);
								
								if(semicolon>quote[0] && semicolon<quote[1]+1){
									sqlb.replace(semicolon,semicolon+1,static_t_replace_str);
									break;
								}
							}
						}
						
						sql=sqlb.toString();
			
						sql=sql.replaceAll(";",static_replace_str);
			
						sql=sql.replaceAll(static_t_replace_str,";");
					
						
						java.util.StringTokenizer st=new java.util.StringTokenizer(sql,static_replace_str);
						
						int allcount=1,ignorecount=0,succeedcount=0,defeatcount=0;;
						try {
								init=new InitialContext();
								String jndi_name = "jdbc/" + datasource;
								if ("TOMCAT".equals(Environment.getWebServerName()))
									jndi_name = "java:comp/env/" + jndi_name;								
								ds=(javax.sql.DataSource)init.lookup(jndi_name);
								conn = ds.getConnection();
								conn.setAutoCommit(false);
								while(st.hasMoreTokens()){
									cursql=st.nextToken();
									if(!cursql.trim().equalsIgnoreCase("commit")){
										ps = conn.prepareStatement(cursql);
										ps.execute();
										cursql="";
										succeedcount++;
									}
									else ignorecount++;
									allcount++;
								}
								conn.commit();
								conn.setAutoCommit(true);
								state="执行成功.....";
						} catch (SQLException e) {
								defeatcount++;
								try {
									conn.rollback();
								} catch (Exception ex) {
									state="事务回滚时产生异常......<BR>错误原因可能是：<BR>"+ex.getMessage();
								}
								
								state="事务已回滚，请调整语句后重新执行.......<BR>目前共执行： "+allcount+" 条语句，其中 "+succeedcount+" 条成功,"+defeatcount+" 条失败,忽略 "+ignorecount+" 条commit语句！<BR>执行数据库操作时出错.....<BR>错误原因可能是：<BR>"+e.getMessage()+"<br>当前执行的语句为："+cursql;
						} finally {
								try {
									if(ps!=null)ps.close();
										if (conn != null) conn.close();
								} catch (Exception ex) {
										out.println(ex.getMessage());
								}
						}
			
				}catch(Exception eq){
					state="语法出错：其中有未闭合的引号<br>异常位置在"+cursql;
				}
			}
		}
}



if(!session.getAttribute("flag").equals("") && !session.getAttribute("flag").equals(sflag)){%>
		<script language="javascript">
			alert("请不要重复刷新页面！！！");
		</script>
<%
	sql="";
}
session.setAttribute("flag",flag);
%>
<script language="javascript">
	function checksubmit(){
		if(form1.sql.value==""){
			alert("请输入语句再提交！");
			return false;
		}
	}
	function selectDMLDDL(type){
		if(type==1){
			document.getElementById("dd1").style.display="";
			document.getElementById("dd2").style.display="none";
		}
		else{
			document.getElementById("dd2").style.display="";
			document.getElementById("dd1").style.display="none";
		}
	}
	
</script>
<form name="form1" method="post" action="sql.jsp" onsubmit="return checksubmit();">
	<table width="100%" border="0" align="center" cellpadding="0" cellspacing="1" bgcolor="#006699">
		<tr>
		<td width="20%" bgcolor="#ffffff" align="center">
			<select name="dmlddl" onclick="selectDMLDDL(this.value);">
				<option value="1" <%=dmlddl.equals("1")?"selected":""%>>　查询语句
				<option value="2" <%=dmlddl.equals("2")?"selected":""%>>　批处理/单行处理
			</select>
		</td>
		<td width="80%" bgcolor="#ffffff" id="dd">
			<div id="dd1" style="display:<%=dmlddl.equals("1")?"":"none"%> ">
				查询数据集，请在文本域中写入查询语句！<font color="#CC0000">注意：查询语句只可单条执行！</font>
			</div>
			<div id="dd2" style="display:<%=dmlddl.equals("2")?"":"none"%> ">
				<input type="radio" name="s_m" value="1" <%=s_m.equals("1")?"checked":""%>>执行单条语句&nbsp;&nbsp;&nbsp;&nbsp;
  			<input type="radio" name="s_m" value="2" <%=s_m.equals("2")?"checked":""%>>执行多条SQL语句（因程序是以[；]分隔，所以触发器最好单条执行）
			</div>
		  </td>
	  </tr>
	  <tr>
		<td bgcolor="#ffffff" align="center">数据中心</td>
		<td bgcolor="#ffffff">
		  <input type="hidden" name="datasource" value="<%=datasource%>">
		  <font color="#CC0000">(当前操作的数据中心为：<%=datasource%>)</font>
		</td>
	  </tr>
	  <tr>
		<td bgcolor="#ffffff" align="center"><font color="#CC0000">填写SQL语句，请注意以<br>分号分隔每条语句</font></td>
		<td bgcolor="#ffffff">
			<table width="100%" border=0>
				<tr>
					<td>
		  				<textarea name="sql" cols="120" rows="20"><%=returnsql%></textarea>
		  			</td>
		  			<td>
		  				<% if ("true".equals(Constants.EAS)) { %>
		  				EAS PORTLET集成SQL<br>
		  				<a href="../application/file_download.jsp?filePath=<%=(System.getProperty("oa_application.config")+"/../../db/mssql/bind/(7)eas_mssql_portlets.sql") %>&fileName=(7)eas_mssql_portlets.sql">Mssql</a>&nbsp;&nbsp;&nbsp;&nbsp;
		  				<a href="../application/file_download.jsp?filePath=<%=(System.getProperty("oa_application.config")+"/../../db/oracle/bind/(7)eas_oracle_portlets.sql") %>&fileName=(7)eas_oracle_portlets.sql">Oracle</a><br><br>
		  				EAS OA论坛集成SQL<br>
		  				<a href="../application/file_download.jsp?filePath=<%=(System.getProperty("oa_application.config")+"/../../db/mssql/bind/(8)eas_mssql_yr_bbs.sql") %>&fileName=(8)eas_mssql_yr_bbs.sql">Mssql</a>&nbsp;&nbsp;&nbsp;&nbsp;
		  				<a href="../application/file_download.jsp?filePath=<%=(System.getProperty("oa_application.config")+"/../../db/oracle/bind/(8)eas_oracle_yr_bbs.sql") %>&fileName=(8)eas_oracle_yr_bbs.sql">Oracle</a><br>
		  				<% } %>
		  			</td>
		  		</tr>
		  	</table>
		</td>
	  </tr>
	  
	  <tr>
		<td bgcolor="#ffffff" align="right">&nbsp;</td>
		<td align="center" bgcolor="#ffffff">
			<input type="submit" name="Submit" value="执行SQL">	    
			<input type="reset" name="reset" value="重置表单">
			<input type="button" name="retback" value="返回列表"
				onclick="location='index.jsp';">
		</td>
	  </tr>
	</table>
	<input type="hidden" name="flag" value="<%=flag%>">
</form>
<%=state%>
<BR>
</body>
</html>
